﻿Imports System.Data.SqlClient

Public Class StageForm

    Public StageHeader As String
    Public CurrentStage As Integer
    Dim CurrentStep As Integer
    Dim AddMode As Boolean
    Public Who As String


    Private Sub CloseButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CloseButton.Click

        If DescriptionBox.Enabled = False Then
            Me.Close()
            Exit Sub
        End If

        If StepsGrid.RowCount <> 0 Then
            If Trim(DescriptionBox.Text) = "" Then
                MessageBox.Show("You have not entered a Product Name.  Please enter a Product Name.", "No Product Name", MessageBoxButtons.OK, MessageBoxIcon.Error)
                DescriptionBox.Focus()
                Exit Sub
            End If
        Else
            'delete the product, but ONLY if this is a product we are adding
            If Who <> "Stage" Then
                Dim sql As String

                sql = "delete from Modelling_Products " & _
                      "where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' " & _
                      "and Product_Number = " & CurrentStage.ToString

                RunNonQuery(sql)
            End If
            
        End If

        Me.Close()

    End Sub

    Private Sub StageForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Job("Preparing " & Who & "...")
        AddMode = False
        CurrentStep = 1
        LoadStepsGrid()
        LoadCatBoxes()
        OperatorBox.SelectedIndex = 0
        IfBox.SelectedIndex = 0

        If StepsGrid.RowCount <> 0 Then
            StepsGrid.CurrentCell = StepsGrid.Item(StepsGrid.Columns("Step").Index, 0)

        End If
        Job("Ready")
    End Sub

    Private Sub LoadStepsGrid()
        Dim sql As String

        '     If Who = "Stage" Then
        sql = "select s.*, " & _
"	case " & _
"		when Step_Number = 0 then 'Step 1 a'  " & _
"		when Step_Number = 1 then 'Step 1 b'  " & _
"		else 'Step ' + cast(Step_Number as varchar(3)) " & _
"		end as Step,  " & _
" " & _
"  case " & _
"		when Step_Number = 0 then 'Start with'  " & _
"		when Step_Number = 1 then 'Step 1 a'  " & _
"		else 'Step ' + cast(Step_Number - 1 as varchar(3))  " & _
"	end as Part1,  " & _
" " & _
"	Operator as O,  " & _
" " & _
"  case  " & _
"       when s.Table_Name = 'zzConstant' then cast(s.Constant as nvarchar(max))  " & _
"           when s.Table_Name = 'aaProducts' then 'Product ' + Prod.Product_Description  " & _
"           when s.Table_Name = 'bbStageData' then  bbStage.bbDescription  " & _
"       else  Description  + ' ' + Table_Description " & _
"   end + " & _
"    " & _
"   case " & _
"       when s.Table_Name_Exception = 'zzConstant' then ' (use ' + cast(s.Constant_Exception as nvarchar(max)) + ' as an exception)' " & _
"       when s.Table_Name_Exception = 'aaProducts' then ' (use ' + Product_Description_Exception + ' as an exception)' " & _
"       when s.Table_Name_Exception is null then ' (use ' + coalesce(DataCut_Exception_Description, 'nothing') + ' as an exception)'  " & _
"       when s.Table_Name_Exception = 'bbStageData' then ' (use ' + bbStage_Exception.bbDescription + ' as an exception)' " & _
"       when s.Table_Name_Exception <> 'none' then ' (use ' + DataCut_Exception_Description + ' as an exception)' " & _
"       else " & _
"         ' (use nothing as an exception)' " & _
"   end as Part2, " & _
" " & _
"    case   " & _
"		when Condition = 'no' then ''   " & _
"            when Table_Name_Condition = 'aaProducts' then 'when the answer is ' + Condition + ' ' + Product_Description_Condition + ', use '   " & _
"            when Table_Name_Condition = 'zzConstant' then 'when the answer is ' + Condition + ' ' + rtrim(DataCut_ID_Condition) + ', use '   " & _
"            when s.Table_Name_Condition = 'bbStageData' then 'when the answer is ' + Condition + bbStage_Condition.bbDescription + ', use '  " & _
"		else 'when the answer is ' + Condition + ' ' + DataCut_Condition_Description + ' ' + Table_Description_Condition + ', use '   " & _
"	end + " & _
"    case   " & _
"		when Condition = 'no' then ''  " & _
"            when Table_Name_Condition_Value = 'aaProducts' then Product_Description_Condition_Value  " & _
"            when Table_Name_Condition_Value = 'zzConstant' then rtrim(DataCut_ID_Condition_Value)  " & _
"            when s.Table_Name_Condition_Value = 'bbStageData' then  bbStage_Condition_Val.bbDescription  " & _
"		else DataCut_Condition_Description_Value  " & _
"	end " & _
" as Part3, cast(null as Image) as ThePic  " & _
" " & _
IIf(Who = "Stage", "from dbo.Modelling_Steps s  ", "from Modelling_Steps_Products s ") & _
"" & _
"" & _
"left outer join " & _
"(select ltrim(rtrim(Scenario_ID)) + ltrim(rtrim(s.Allocation_ID)) + ltrim(rtrim(Stage_Number)) as Datacut_ID, " & _
                        "Allocation_Description + ' ' + ltrim(rtrim(stage_number)) + ': ' + Stage_description as bbDescription " & _
                        "from dbo.Modelling_Stages s " & _
                        "inner join allocation_lines a on (a.Allocation_ID = s.Allocation_ID) " & _
                        "where stage_description <> '' " & _
                        "and Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "') bbStage " & _
" on (s.Datacut_ID = bbStage.Datacut_ID)" & _
"" & _
"left outer join " & _
"(select ltrim(rtrim(Scenario_ID)) + ltrim(rtrim(s.Allocation_ID)) + ltrim(rtrim(Stage_Number)) as Datacut_ID, " & _
                        "Allocation_Description + ' ' + ltrim(rtrim(stage_number)) + ': ' + Stage_description as bbDescription " & _
                        "from dbo.Modelling_Stages s " & _
                        "inner join allocation_lines a on (a.Allocation_ID = s.Allocation_ID) " & _
                        "where stage_description <> '' " & _
                        "and Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "') bbStage_Exception " & _
" on (s.Datacut_ID_Exception = bbStage_Exception.Datacut_ID)" & _
"" & _
"left outer join " & _
"(select ltrim(rtrim(Scenario_ID)) + ltrim(rtrim(s.Allocation_ID)) + ltrim(rtrim(Stage_Number)) as Datacut_ID, " & _
                        "Allocation_Description + ' ' + ltrim(rtrim(stage_number)) + ': ' + Stage_description as bbDescription " & _
                        "from dbo.Modelling_Stages s " & _
                        "inner join allocation_lines a on (a.Allocation_ID = s.Allocation_ID) " & _
                        "where stage_description <> '' " & _
                        "and Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "') bbStage_Condition " & _
" on (s.Datacut_ID_Condition = bbStage_Condition.Datacut_ID)" & _
"" & _
"left outer join " & _
"(select ltrim(rtrim(Scenario_ID)) + ltrim(rtrim(s.Allocation_ID)) + ltrim(rtrim(Stage_Number)) as Datacut_ID, " & _
                        "Allocation_Description + ' ' + ltrim(rtrim(stage_number)) + ': ' + Stage_description as bbDescription " & _
                        "from dbo.Modelling_Stages s " & _
                        "inner join allocation_lines a on (a.Allocation_ID = s.Allocation_ID) " & _
                        "where stage_description <> '' " & _
                        "and Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "') bbStage_Condition_Val " & _
" on (s.Datacut_ID_Condition_Value = bbStage_Condition_Val.Datacut_ID)" & _
"" & _
"left outer join  " & _
"(select Table_Name, Table_Description as Table_Description_Condition from Modelling_TableList) t2  " & _
"on (t2.Table_Name = s.Table_name_Condition)  " & _
"left outer join  " & _
"(select Table_Name, DataCut_ID, Description as DataCut_Exception_Description from DataCuts) IDs2  " & _
"on (IDs2.DataCut_ID = s.DataCut_ID_Exception and IDs2.Table_Name = s.Table_Name_Exception)  " & _
"left outer join  " & _
"(select Table_Name, DataCut_ID, Description as DataCut_Condition_Description from DataCuts) IDs3  " & _
"on (IDs3.DataCut_ID = s.DataCut_ID_Condition and IDS3.Table_Name = s.Table_Name_Condition)  " & _
"left outer join  " & _
"(select Table_Name, DataCut_ID, Description as DataCut_Condition_Description_Value from DataCuts) IDs4  " & _
"on (IDs4.DataCut_ID = s.DataCut_ID_Condition_Value and IDS4.Table_Name = s.Table_Name_Condition_Value)  " & _
"     left outer join  " & _
"     (select cast(Product_Number as nvarchar(35)) as Product_Number, Product_Description from Modelling_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "') Prod  " & _
"     on (Prod.Product_Number = s.DataCut_ID)  " & _
"     left outer join  " & _
"     (select cast(Product_Number as nvarchar(35)) as Product_Number, Product_Description as Product_Description_Exception from Modelling_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "') Prod_Exception  " & _
"     on (Prod_Exception.Product_Number = s.DataCut_ID_Exception)  " & _
"     left outer join  " & _
"     (select cast(Product_Number as nvarchar(35)) as Product_Number, Product_Description as Product_Description_Condition from Modelling_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "') Prod_Condition  " & _
"     on (Prod_Condition.Product_Number = s.DataCut_ID_Condition)  " & _
"     left outer join  " & _
"     (select cast(Product_Number as nvarchar(35)) as Product_Number, Product_Description as Product_Description_Condition_Value from Modelling_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "') Prod_Condition_Val  " & _
"     on (Prod_Condition_Val.Product_Number = s.DataCut_ID_Condition_Value)  " & _
"left outer join  " & _
"DataCuts IDs on (IDs.DataCut_ID = s.DataCut_ID and IDs.Table_Name = s.Table_Name),  " & _
"(select Table_Name, Table_Description from Modelling_TableList union select 'zzConstant' as Table_Name, 'Constant' as Table_Description union select 'aaProducts' as Table_Name, 'Products' as Table_Description union select 'bbStageData' as Table_Name, 'Stages' as Table_Description) t  " & _
" " & _
"where " & _
"t.Table_Name = s.Table_name " & _
"and s.Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "

        If Who = "Stage" Then
            sql = sql & " and s.Stage_Number = " & CurrentStage.ToString & _
          " and s.Allocation_ID = '" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "' " & _
          " order by s.Step_Number"
        Else
            sql = sql & " and s.Product_Number = " & CurrentStage.ToString & _
          " order by s.Step_Number"
        End If

       


        StepsGrid.DataSource = LoadDataView(sql)

        'set all hidden and then show the 2 columns
        For i = 0 To StepsGrid.ColumnCount - 1
            StepsGrid.Columns(i).Visible = False
        Next

        StepsGrid.Columns("Step").Visible = True
        StepsGrid.Columns("Part1").Visible = True
        StepsGrid.Columns("O").Visible = True
        StepsGrid.Columns("Part2").Visible = True
        StepsGrid.Columns("Part3").Visible = True
        StepsGrid.Columns("ThePic").Visible = True


        StepsGrid.RowHeadersVisible = False
        StepsGrid.ColumnHeadersVisible = False

        If StepsGrid.RowCount = 0 Then
            ButtonPanel.Enabled = False
        Else
            ButtonPanel.Enabled = True
        End If
    End Sub

    Private Function HaveLearners() As Boolean
        Dim i, Learner As Integer

        If StepsGrid.RowCount = 0 Then
            HaveLearners = False
        Else
            Learner = 0
            For i = 0 To StepsGrid.RowCount - 1
                Learner = Learner + StepsGrid.Item(StepsGrid.Columns("Learner_Flag").Index, i).Value
            Next
            If Learner > 0 Then
                HaveLearners = True
            Else
                HaveLearners = False
            End If
        End If
    End Function

    Private Sub DescriptionBox_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DescriptionBox.KeyUp

        If Who = "Stage" Then
            If DescriptionBox.Text = "" Then
                Me.Text = StageHeader

            Else
                Me.Text = StageHeader & " - " & DescriptionBox.Text
            End If

        Else
            If DescriptionBox.Text = "" Then
                Me.Text = "Product"

            Else
                Me.Text = "Product - " & DescriptionBox.Text
            End If
        End If


    End Sub

    Private Sub LoadCutBox1()
        Dim sql As String

        If CatBox1.SelectedIndex < 0 Then Exit Sub

        If CatBox1.SelectedValue.ToString = "System.Data.DataRowView" Then Exit Sub

        If CatBox1.SelectedValue.ToString = "zzConstant" Then
            CutBox1.DropDownStyle = ComboBoxStyle.DropDown

            CutBox1.DataSource = Nothing
            '    ExceptBox1.DataSource = Nothing


        Else
            CutBox1.DropDownStyle = ComboBoxStyle.DropDownList
            If CatBox1.SelectedValue.ToString = "aaProducts" Then
                sql = "select Product_Number as DataCut_ID, Product_Description as Description from Modelling_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "

                If Who <> "Stage" Then
                    sql = sql & " and Product_Number <> " & CurrentStage
                End If
            Else
                If CatBox1.SelectedValue.ToString = "bbStageData" Then

                    sql = "select ltrim(rtrim(Scenario_ID)) + ltrim(rtrim(s.Allocation_ID)) + ltrim(rtrim(Stage_Number)) as Datacut_ID, " & _
                            "Allocation_Description + ' ' + ltrim(rtrim(stage_number)) + ': ' + Stage_description as Description " & _
                            "from dbo.Modelling_Stages s " & _
                            "inner join allocation_lines a on (a.Allocation_ID = s.Allocation_ID) " & _
                            "where stage_description <> '' " & _
                            "and Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "



                    If Who = "Stage" Then
                        sql = sql & " and (Stage_Number <> " & CurrentStage
                        sql = sql & " or s.Allocation_ID <> '" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "') "
                    End If

                    'sql = sql & " order by s.allocation_id, stage_number "

                Else
                    sql = "select distinct DataCut_ID, Description from " & CatBox1.SelectedValue.ToString
                    sql = sql & " where Source not like 'Case%' union all " & _
                      "select ids.DataCut_ID, Description from dbo.Case_Templates_IDs IDS, dbo.Case_Templates temp where ids.DataCut_ID = temp.DataCut_ID and Table_Name = '" & CatBox1.SelectedValue.ToString & "'"

                    sql = sql & " union all " & _
                      "select DataCut_ID, Description from dbo.Modelling_Case_Datasets where datacut_id in (select distinct datacut_id from dbo.Modelling_Case_Templates where Modelling_table = '" & CatBox1.SelectedValue.ToString & "') "


                    If CatBox1.SelectedValue.ToString = "Modelling_Other" Then
                        sql = sql & " union all " & _
                          "select ids.DataCut_ID, Description from dbo.Case_Templates_IDs IDS, dbo.Case_Templates temp where ids.DataCut_ID = temp.DataCut_ID and Table_Name = 'Modelling_Products'"
                    End If
                End If

            End If

            sql = sql & " order by Description "

            CutBox1.DataSource = LoadDataView(sql)
            CutBox1.ValueMember = "DataCut_ID"
            CutBox1.DisplayMember = "Description"

        End If


    End Sub

    Private Sub LoadExceptCutBox1()
        Dim sql As String



        If ExceptCatBox1.SelectedValue.ToString = "aaProducts" Then
            sql = ""

            sql = sql & " select cast(Product_Number as varchar(35)) as DataCut_ID, Product_Description as Description from Modelling_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "

            If Who <> "Stage" Then
                sql = sql & " and Product_Number <> " & CurrentStage
            End If

        Else

            If ExceptCatBox1.SelectedValue.ToString = "bbStageData" Then

                sql = "select ltrim(rtrim(Scenario_ID)) + ltrim(rtrim(s.Allocation_ID)) + ltrim(rtrim(Stage_Number)) as Datacut_ID, " & _
                        "Allocation_Description + ' ' + ltrim(rtrim(stage_number)) + ': ' + Stage_description as Description " & _
                        "from dbo.Modelling_Stages s " & _
                        "inner join allocation_lines a on (a.Allocation_ID = s.Allocation_ID) " & _
                        "where stage_description <> '' " & _
                        "and Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "



                If Who = "Stage" Then
                    sql = sql & " and (Stage_Number <> " & CurrentStage
                    sql = sql & " or s.Allocation_ID <> '" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "') "
                End If
            Else

                sql = "select ids.DataCut_ID, Description from dbo.Case_Templates_IDs IDS, dbo.Case_Templates temp where ids.DataCut_ID = temp.DataCut_ID and Table_Name = '" & ExceptCatBox1.SelectedValue.ToString & "'"


                sql = sql & " union all " & _
                      "select DataCut_ID, Description from dbo.Modelling_Case_Datasets where datacut_id in (select distinct datacut_id from dbo.Modelling_Case_Templates where Modelling_table = '" & ExceptCatBox1.SelectedValue.ToString & "') "


                If ExceptCatBox1.SelectedValue.ToString = "Modelling_Other" Then
                    sql = sql & " union all " & _
                      "select ids.DataCut_ID, Description from dbo.Case_Templates_IDs IDS, dbo.Case_Templates temp where ids.DataCut_ID = temp.DataCut_ID and Table_Name = 'Modelling_Products'"
                End If

                If ExceptCatBox1.SelectedValue.ToString = "none" Then
                    sql = "select 'none' as DataCut_ID, '<None>' as Description "
                End If

            End If



            


        End If

        sql = sql & " order by Description "



        If ExceptCatBox1.SelectedValue.ToString = "zzConstant" Then
            ExceptBox1.DropDownStyle = ComboBoxStyle.DropDown

            ExceptBox1.DataSource = Nothing
            '    ExceptBox1.DataSource = Nothing
        Else
            ExceptBox1.DropDownStyle = ComboBoxStyle.DropDownList
            ExceptBox1.DataSource = LoadDataView(sql)
            ExceptBox1.ValueMember = "DataCut_ID"
            ExceptBox1.DisplayMember = "Description"
        End If
    End Sub


    Private Sub LoadExceptCutBox2()
        Dim sql As String

        If ExceptCatBox2.SelectedValue.ToString = "aaProducts" Then
            sql = ""

            sql = sql & " select cast(Product_Number as varchar(35)) as DataCut_ID, Product_Description as Description from Modelling_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "

            If Who <> "Stage" Then
                sql = sql & " and Product_Number <> " & CurrentStage
            End If
        Else

            If ExceptCatBox2.SelectedValue.ToString = "bbStageData" Then

                sql = "select ltrim(rtrim(Scenario_ID)) + ltrim(rtrim(s.Allocation_ID)) + ltrim(rtrim(Stage_Number)) as Datacut_ID, " & _
                        "Allocation_Description + ' ' + ltrim(rtrim(stage_number)) + ': ' + Stage_description as Description " & _
                        "from dbo.Modelling_Stages s " & _
                        "inner join allocation_lines a on (a.Allocation_ID = s.Allocation_ID) " & _
                        "where stage_description <> '' " & _
                        "and Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "



                If Who = "Stage" Then
                    sql = sql & " and (Stage_Number <> " & CurrentStage
                    sql = sql & " or s.Allocation_ID <> '" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "') "
                End If
            Else
                sql = "select ids.DataCut_ID, Description from dbo.Case_Templates_IDs IDS, dbo.Case_Templates temp where ids.DataCut_ID = temp.DataCut_ID and Table_Name = '" & ExceptCatBox2.SelectedValue.ToString & "'"

                sql = sql & " union all " & _
                      "select DataCut_ID, Description from dbo.Modelling_Case_Datasets where datacut_id in (select distinct datacut_id from dbo.Modelling_Case_Templates where Modelling_table = '" & ExceptCatBox2.SelectedValue.ToString & "') "

                If ExceptCatBox2.SelectedValue.ToString = "Modelling_Other" Then
                    sql = sql & " union all " & _
                      "select ids.DataCut_ID, Description from dbo.Case_Templates_IDs IDS, dbo.Case_Templates temp where ids.DataCut_ID = temp.DataCut_ID and Table_Name = 'Modelling_Products'"
                End If

                If ExceptCatBox2.SelectedValue.ToString = "none" Then
                    sql = "select 'none' as DataCut_ID, '<None>' as Description "
                End If

            End If

           

        End If

        sql = sql & " order by Description "

        If ExceptCatBox2.SelectedValue.ToString = "zzConstant" Then
            ExceptBox2.DropDownStyle = ComboBoxStyle.DropDown

            ExceptBox2.DataSource = Nothing

        Else
            ExceptBox2.DropDownStyle = ComboBoxStyle.DropDownList
            ExceptBox2.DataSource = LoadDataView(sql)
            ExceptBox2.ValueMember = "DataCut_ID"
            ExceptBox2.DisplayMember = "Description"
        End If

    End Sub

    Private Sub LoadCutBox2()
        Dim sql As String

        If CatBox2.SelectedIndex < 0 Then Exit Sub

        If CatBox2.SelectedValue.ToString = "System.Data.DataRowView" Then Exit Sub

        If CatBox2.SelectedValue.ToString = "zzConstant" Then
            CutBox2.DropDownStyle = ComboBoxStyle.DropDown

            CutBox2.DataSource = Nothing
            '    ExceptBox2.DataSource = Nothing


        Else
            CutBox2.DropDownStyle = ComboBoxStyle.DropDownList
            If CatBox2.SelectedValue.ToString = "aaProducts" Then
                sql = "select Product_Number as DataCut_ID, Product_Description as Description from Modelling_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "

                If Who <> "Stage" Then
                    sql = sql & " and Product_Number <> " & CurrentStage
                End If
            Else
                If CatBox2.SelectedValue.ToString = "bbStageData" Then

                    sql = "select ltrim(rtrim(Scenario_ID)) + ltrim(rtrim(s.Allocation_ID)) + ltrim(rtrim(Stage_Number)) as Datacut_ID, " & _
                            "Allocation_Description + ' ' + ltrim(rtrim(stage_number)) + ': ' + Stage_description as Description " & _
                            "from dbo.Modelling_Stages s " & _
                            "inner join allocation_lines a on (a.Allocation_ID = s.Allocation_ID) " & _
                            "where stage_description <> '' " & _
                            "and Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "



                    If Who = "Stage" Then
                        sql = sql & " and (Stage_Number <> " & CurrentStage
                        sql = sql & " or s.Allocation_ID <> '" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "') "
                    End If

                    '  sql = sql & " order by s.allocation_id, stage_number "
                Else

                    sql = "select distinct DataCut_ID, Description from " & CatBox2.SelectedValue.ToString
                    sql = sql & " where Source not like 'Case%' union all " & _
                      "select ids.DataCut_ID, Description from dbo.Case_Templates_IDs IDS, dbo.Case_Templates temp where ids.DataCut_ID = temp.DataCut_ID and Table_Name = '" & CatBox2.SelectedValue.ToString & "'"

                    sql = sql & " union all " & _
                      "select DataCut_ID, Description from dbo.Modelling_Case_Datasets where datacut_id in (select distinct datacut_id from dbo.Modelling_Case_Templates where Modelling_table = '" & CatBox2.SelectedValue.ToString & "') "

                    If CatBox2.SelectedValue.ToString = "Modelling_Other" Then
                        sql = sql & " union all " & _
                          "select ids.DataCut_ID, Description from dbo.Case_Templates_IDs IDS, dbo.Case_Templates temp where ids.DataCut_ID = temp.DataCut_ID and Table_Name = 'Modelling_Products'"
                    End If

                End If

            End If

            sql = sql & " order by Description "

            CutBox2.DataSource = LoadDataView(sql)
            CutBox2.ValueMember = "DataCut_ID"
            CutBox2.DisplayMember = "Description"

            '     If CatBox2.SelectedValue.ToString = "aaProducts" Then
            'sql = "select 'none' as DataCut_ID, '<None>' as Description " & _
            '  "union all "

            '            sql = sql & " select cast(Product_Number as varchar(35)) as DataCut_ID, Product_Description as Description from Modelling_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "

            'If Who <> "Stage" Then
            'sql = sql & " and Product_Number <> " & CurrentStage
            'End If
            '   Else
            '  sql = "select 'none' as DataCut_ID, '<None>' as Description " & _
            '   "union all " & _
            '  "select ids.DataCut_ID, Description from dbo.Case_Templates_IDs IDS, dbo.Case_Templates temp where ids.DataCut_ID = temp.DataCut_ID and Table_Name = '" & CatBox2.SelectedValue.ToString & "'"
            'If CatBox2.SelectedValue.ToString = "Modelling_Other" Then
            'sql = sql & " union all " & _
            ' "select ids.DataCut_ID, Description from dbo.Case_Templates_IDs IDS, dbo.Case_Templates temp where ids.DataCut_ID = temp.DataCut_ID and Table_Name = 'Modelling_Products'"
            'End If
            'End If

            '    sql = sql & " order by Description "

            'ExceptBox2.DataSource = LoadDataView(sql)
            'ExceptBox2.ValueMember = "DataCut_ID"
            'ExceptBox2.DisplayMember = "Description"

        End If


    End Sub


    Private Sub LoadCutBox3()
        Dim sql As String

        If CatBox3.SelectedIndex < 0 Then Exit Sub

        If CatBox3.SelectedValue.ToString = "System.Data.DataRowView" Then Exit Sub

        If CatBox3.SelectedValue.ToString = "zzConstant" Then
            CutBox3.DropDownStyle = ComboBoxStyle.DropDown

            CutBox3.DataSource = Nothing
            Exit Sub

        End If

        CutBox3.DropDownStyle = ComboBoxStyle.DropDownList

        If CatBox3.SelectedValue.ToString = "aaProducts" Then
            sql = "select Product_Number as DataCut_ID, Product_Description as Description from Modelling_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "

            If Who <> "Stage" Then
                sql = sql & " and Product_Number <> " & CurrentStage
            End If
        Else

            If CatBox3.SelectedValue.ToString = "bbStageData" Then

                sql = "select ltrim(rtrim(Scenario_ID)) + ltrim(rtrim(s.Allocation_ID)) + ltrim(rtrim(Stage_Number)) as Datacut_ID, " & _
                        "Allocation_Description + ' ' + ltrim(rtrim(stage_number)) + ': ' + Stage_description as Description " & _
                        "from dbo.Modelling_Stages s " & _
                        "inner join allocation_lines a on (a.Allocation_ID = s.Allocation_ID) " & _
                        "where stage_description <> '' " & _
                        "and Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "



                If Who = "Stage" Then
                    sql = sql & " and (Stage_Number <> " & CurrentStage
                    sql = sql & " or s.Allocation_ID <> '" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "') "
                End If

                '  sql = sql & " order by s.allocation_id, stage_number "
            Else
                sql = "select distinct DataCut_ID, Description from " & CatBox3.SelectedValue.ToString
            End If
        End If

        sql = sql & " order by Description "

        CutBox3.DataSource = LoadDataView(sql)
        CutBox3.ValueMember = "DataCut_ID"
        CutBox3.DisplayMember = "Description"


    End Sub

    Private Sub LoadCutBox4()
        Dim sql As String

        If CatBox4.SelectedIndex < 0 Then Exit Sub

        If CatBox4.SelectedValue.ToString = "System.Data.DataRowView" Then Exit Sub

        If CatBox4.SelectedValue.ToString = "zzConstant" Then
            CutBox4.DropDownStyle = ComboBoxStyle.DropDown

            CutBox4.DataSource = Nothing
            Exit Sub

        End If

        CutBox4.DropDownStyle = ComboBoxStyle.DropDownList

        If CatBox4.SelectedValue.ToString = "aaProducts" Then
            sql = "select Product_Number as DataCut_ID, Product_Description as Description from Modelling_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "

            If Who <> "Stage" Then
                sql = sql & " and Product_Number <> " & CurrentStage
            End If
        Else
            If CatBox4.SelectedValue.ToString = "bbStageData" Then

                sql = "select ltrim(rtrim(Scenario_ID)) + ltrim(rtrim(s.Allocation_ID)) + ltrim(rtrim(Stage_Number)) as Datacut_ID, " & _
                        "Allocation_Description + ' ' + ltrim(rtrim(stage_number)) + ': ' + Stage_description as Description " & _
                        "from dbo.Modelling_Stages s " & _
                        "inner join allocation_lines a on (a.Allocation_ID = s.Allocation_ID) " & _
                        "where stage_description <> '' " & _
                        "and Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "



                If Who = "Stage" Then
                    sql = sql & " and (Stage_Number <> " & CurrentStage
                    sql = sql & " or s.Allocation_ID <> '" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "') "
                End If

                '  sql = sql & " order by s.allocation_id, stage_number "
            Else
                sql = "select distinct DataCut_ID, Description from " & CatBox4.SelectedValue.ToString
            End If

        End If

        sql = sql & " order by Description "

        CutBox4.DataSource = LoadDataView(sql)
        CutBox4.ValueMember = "DataCut_ID"
        CutBox4.DisplayMember = "Description"


    End Sub

    Private Sub LoadCatBoxes()
        Dim sql As String
        Dim ProductsCount As Integer
        Dim StagesCount As Integer
        Dim TheView As DataView

        ProductsCount = 0
        StagesCount = 0

        'check to see if there are any products
        sql = "Select * from Modelling_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "

        If Who <> "Stage" Then
            sql = sql & " and Product_Number <> " & CurrentStage

        End If

        TheView = LoadDataView(sql)

        ProductsCount = TheView.Count
        TheView = Nothing

        'check stages
        sql = "Select * from Modelling_Stages where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "

        If Who <> "Stage" Then
            sql = sql & " and (Stage_Number <> " & CurrentStage
            sql = sql & " or Allocation_ID <> '" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "') "
        End If

        TheView = LoadDataView(sql)

        StagesCount = TheView.Count
        TheView = Nothing


        sql = "select * from dbo.Modelling_TableList"

        If ProductsCount <> 0 Then
            sql = sql & " union select 'aaProducts' as Table_Name, 'Products' as Table_Description"
        End If

     
        sql = "select Table_Name, Table_Description from dbo.Modelling_TableList " & _
              "union select 'zzConstant' as Table_Name, 'Constant' as Table_Description"

        If ProductsCount <> 0 Then
            sql = sql & " union select 'aaProducts' as Table_Name, 'Products' as Table_Description"
        End If

        'add stages as well

        If StagesCount <> 0 Then
            sql = sql & " union select 'bbStageData' as Table_Name, 'Stages' as Table_Description"
        End If

        CatBox2.DataSource = LoadDataView(sql)
        CatBox2.ValueMember = "Table_Name"
        CatBox2.DisplayMember = "Table_Description"

        'Actualy no, its the same
        CatBox1.DataSource = LoadDataView(sql)
        CatBox1.ValueMember = "Table_Name"
        CatBox1.DisplayMember = "Table_Description"


        CatBox3.DataSource = LoadDataView(sql)
        CatBox3.ValueMember = "Table_Name"
        CatBox3.DisplayMember = "Table_Description"

        CatBox3.SelectedValue = "Modelling_Factors"

        CatBox4.DataSource = LoadDataView(sql)
        CatBox4.ValueMember = "Table_Name"
        CatBox4.DisplayMember = "Table_Description"

        CatBox4.SelectedValue = "Modelling_Factors"

        'and now the except cat boxes

        sql = "select 'none' as Table_Name, '<None>' as Table_Description union " & sql & " order by Table_Description"

        ExceptCatBox1.DataSource = LoadDataView(sql)
        ExceptCatBox1.ValueMember = "Table_Name"
        ExceptCatBox1.DisplayMember = "Table_Description"

        ExceptCatBox2.DataSource = LoadDataView(sql)
        ExceptCatBox2.ValueMember = "Table_Name"
        ExceptCatBox2.DisplayMember = "Table_Description"
    End Sub

    Private Sub CatBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CatBox1.SelectedIndexChanged
        LoadCutBox1()

    End Sub

    Private Sub CatBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CatBox2.SelectedIndexChanged
        LoadCutBox2()

    End Sub

    Private Sub CatBox3_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CatBox3.SelectedIndexChanged
        LoadCutBox3()

    End Sub

    Private Sub AddStep()
        If StepsGrid.RowCount = 0 Then Exit Sub
        StepsGrid.CurrentCell = StepsGrid.Item(StepsGrid.Columns("Step").Index, StepsGrid.RowCount - 1)
        AddMode = True

        CurrentStep = StepsGrid.RowCount

        LoadStep(CurrentStep)

        StepLabel.Text = "Step " & CurrentStep.ToString & " (new)"
        AnswerLabel.Text = "Answer from Step " & CurrentStep - 1

        ButtonPanel.Enabled = False
        StepsGrid.Enabled = False

        LearnerCheckBox.Checked = False

        If HaveLearners() Then
            LearnerCheckBox.Enabled = False

        Else
            LearnerCheckBox.Enabled = True
        End If


    End Sub

    Private Function SetOperatorBox(ByVal TheOperator As String) As Integer
        Dim TheIndex As Integer

        Select Case TheOperator
            Case "/"
                TheIndex = 0
            Case "x"
                TheIndex = 1
            Case "-"
                TheIndex = 2
            Case Else
                TheIndex = 3
        End Select

        SetOperatorBox = TheIndex

    End Function

    Private Function SetConditionBox(ByVal TheCon As String) As Integer
        Dim TheIndex As Integer

        Select Case TheCon
            Case "no"
                TheIndex = 0
            Case "= "
                TheIndex = 1
            Case "> "
                TheIndex = 2
            Case "< "
                TheIndex = 3
            Case ">="
                TheIndex = 4
            Case "<="
                TheIndex = 5
            Case "<>"
                TheIndex = 6
            Case Else
                TheIndex = 0
        End Select

        SetConditionBox = TheIndex

    End Function


    Private Sub LoadStep(ByVal StepNumber As Integer)
        If StepsGrid.RowCount < 2 Then Exit Sub

        StepLabel.Text = "Step " & StepNumber.ToString

        If StepNumber = 1 Then
            Step1Panel.Visible = True
            AnswerPanel.Visible = False
        Else
            Step1Panel.Visible = False
            AnswerPanel.Visible = True
            AnswerLabel.Text = "Answer from Step " & StepNumber - 1
        End If

        If StepNumber = 1 Then
            CatBox1.SelectedValue = StepsGrid.Item(StepsGrid.Columns("Table_Name").Index, StepNumber - 1).Value

            If CatBox1.SelectedValue.ToString = "zzConstant" Then
                CutBox1.DropDownStyle = ComboBoxStyle.DropDown
                CutBox1.Text = StepsGrid.Item(StepsGrid.Columns("Constant").Index, StepNumber - 1).Value.ToString
            Else
                CutBox1.DropDownStyle = ComboBoxStyle.DropDownList

                CutBox1.SelectedValue = StepsGrid.Item(StepsGrid.Columns("Datacut_ID").Index, StepNumber - 1).Value
            End If

            'check to see if this is an old stage/product
            If IsDBNull(StepsGrid.Item(StepsGrid.Columns("Table_Name_Exception").Index, StepNumber - 1).Value) Then
                If Trim(StepsGrid.Item(StepsGrid.Columns("DataCut_ID_Exception").Index, StepNumber - 1).Value) = "none" Then
                    ExceptCatBox1.SelectedValue = "none"
                Else
                    ExceptCatBox1.SelectedValue = StepsGrid.Item(StepsGrid.Columns("Table_Name").Index, StepNumber - 1).Value
                End If

            Else

                ExceptCatBox1.SelectedValue = StepsGrid.Item(StepsGrid.Columns("Table_Name_Exception").Index, StepNumber - 1).Value

            End If

            If ExceptCatBox1.SelectedValue = "zzConstant" Then
                ExceptBox1.DropDownStyle = ComboBoxStyle.DropDown
                ExceptBox1.Text = StepsGrid.Item(StepsGrid.Columns("Constant_Exception").Index, StepNumber - 1).Value
            Else
                ExceptBox1.DropDownStyle = ComboBoxStyle.DropDownList
                ExceptBox1.SelectedValue = StepsGrid.Item(StepsGrid.Columns("DataCut_ID_Exception").Index, StepNumber - 1).Value
            End If




        End If

        If AddMode = False Then

            OperatorBox.SelectedIndex = SetOperatorBox(StepsGrid.Item(StepsGrid.Columns("O").Index, StepNumber).Value)

            CatBox2.SelectedValue = StepsGrid.Item(StepsGrid.Columns("Table_Name").Index, StepNumber).Value

            ' MessageBox.Show(StepsGrid.Item(StepsGrid.Columns("Table_Name").Index, StepNumber).Value)

            If CatBox2.SelectedValue.ToString = "zzConstant" Then
                CutBox2.DropDownStyle = ComboBoxStyle.DropDown
                CutBox2.Text = StepsGrid.Item(StepsGrid.Columns("Constant").Index, StepNumber).Value.ToString
            Else
                CutBox2.DropDownStyle = ComboBoxStyle.DropDownList

                CutBox2.SelectedValue = StepsGrid.Item(StepsGrid.Columns("Datacut_ID").Index, StepNumber).Value
            End If
            'check to see if this is an old stage/product
            If IsDBNull(StepsGrid.Item(StepsGrid.Columns("Table_Name_Exception").Index, StepNumber).Value) Then
                If Trim(StepsGrid.Item(StepsGrid.Columns("DataCut_ID_Exception").Index, StepNumber).Value) = "none" Then
                    ExceptCatBox2.SelectedValue = "none"
                Else
                    ExceptCatBox2.SelectedValue = StepsGrid.Item(StepsGrid.Columns("Table_Name").Index, StepNumber).Value
                End If
            Else
                ExceptCatBox2.SelectedValue = StepsGrid.Item(StepsGrid.Columns("Table_Name_Exception").Index, StepNumber).Value
            End If

            If ExceptCatBox2.SelectedValue = "zzConstant" Then
                ExceptBox2.DropDownStyle = ComboBoxStyle.DropDown
                ExceptBox2.Text = StepsGrid.Item(StepsGrid.Columns("Constant_Exception").Index, StepNumber).Value
            Else
                ExceptBox2.DropDownStyle = ComboBoxStyle.DropDownList
                ExceptBox2.SelectedValue = StepsGrid.Item(StepsGrid.Columns("DataCut_ID_Exception").Index, StepNumber).Value
            End If




            IfBox.SelectedIndex = SetConditionBox(StepsGrid.Item(StepsGrid.Columns("Condition").Index, StepNumber).Value)

            If IfBox.SelectedIndex <> 0 Then
                CatBox3.SelectedValue = StepsGrid.Item(StepsGrid.Columns("Table_Name_Condition").Index, StepNumber).Value
                If CatBox3.SelectedValue.ToString = "zzConstant" Then
                    CutBox3.DropDownStyle = ComboBoxStyle.DropDown
                    CutBox3.Text = RTrim(StepsGrid.Item(StepsGrid.Columns("Datacut_ID_Condition").Index, StepNumber).Value.ToString)
                Else
                    CutBox3.DropDownStyle = ComboBoxStyle.DropDownList
                    CutBox3.SelectedValue = StepsGrid.Item(StepsGrid.Columns("Datacut_ID_Condition").Index, StepNumber).Value
                End If

                CatBox4.SelectedValue = StepsGrid.Item(StepsGrid.Columns("Table_Name_Condition_Value").Index, StepNumber).Value
                If CatBox4.SelectedValue.ToString = "zzConstant" Then
                    CutBox4.DropDownStyle = ComboBoxStyle.DropDown
                    CutBox4.Text = RTrim(StepsGrid.Item(StepsGrid.Columns("Datacut_ID_Condition_Value").Index, StepNumber).Value.ToString)
                Else
                    CutBox4.DropDownStyle = ComboBoxStyle.DropDownList
                    CutBox4.SelectedValue = StepsGrid.Item(StepsGrid.Columns("Datacut_ID_Condition_Value").Index, StepNumber).Value
                End If

            End If

            LearnerCheckBox.Enabled = True
            If StepsGrid.Item(StepsGrid.Columns("Learner_Flag").Index, StepsGrid.CurrentRow.Index).Value = 1 Then
                LearnerCheckBox.Checked = True

            Else
                LearnerCheckBox.Checked = False
                If HaveLearners() Then
                    LearnerCheckBox.Enabled = False

                Else
                    LearnerCheckBox.Enabled = True
                End If
            End If
        End If
    End Sub

    Private Sub AddBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddBut.Click

        AddStep()

        IfBox.SelectedIndex = 0
    End Sub

    Private Sub IfBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles IfBox.SelectedIndexChanged
        'if box conditioning

        If IfBox.Text = "no condition" Then
            CatBox3.Enabled = False
            CutBox3.Enabled = False
            ConditionGroup.Enabled = False
        Else
            CatBox3.Enabled = True
            CutBox3.Enabled = True
            ConditionGroup.Enabled = True
        End If
    End Sub

    Private Sub SaveStep()
        Dim sql As String
        Dim LearnerFlag As Integer
        Dim ExceptionConstant As Decimal
        Dim ExceptionValue As String

        If DescriptionBox.Enabled = False Then Exit Sub

        'if this is step 1 then add 2 rows

        'first delete in case its an update
        If Who = "Stage" Then
            sql = "delete from Modelling_Steps where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "
        Else
            sql = "delete from Modelling_Steps_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "
        End If

        sql = sql & " and " & Who & "_Number = " & CurrentStage.ToString & _
             " and Step_Number = " & CurrentStep.ToString
        If Who = "Stage" Then sql = sql & " and Allocation_ID = '" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "' "

        RunNonQuery(sql)

        If CurrentStep = 1 Then
            If Who = "Stage" Then
                sql = "delete from Modelling_Steps where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "
            Else
                sql = "delete from Modelling_Steps_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' "
            End If

            sql = sql & " and " & Who & "_Number = " & CurrentStage.ToString & _
                 " and Step_Number = 0"
            If Who = "Stage" Then sql = sql & " and Allocation_ID = '" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "' "

            RunNonQuery(sql)
        End If

        'now add the row

        If CurrentStep = 1 Then

            'exceptional constant
            Try
                ExceptionConstant = ExceptBox1.Text

            Catch ex As Exception
                'was not successful conversion so make it 0
                ExceptionConstant = 0
            End Try

            If IsNothing(ExceptBox1.SelectedValue) Then
                ExceptionValue = ""
            Else
                ExceptionValue = ExceptBox1.SelectedValue.ToString
            End If

            If CatBox1.SelectedValue.ToString <> "zzConstant" Then
                If Who = "Stage" Then
                    sql = "insert into Modelling_Steps " & _
                  " (Scenario_ID, Allocation_ID, Stage_Number, Step_Number, Operator, Table_Name, DataCut_ID, DataCut_ID_Exception, " & _
                  " Condition, Table_Name_Condition, DataCut_ID_Condition, Constant, Constant_Exception, Learner_Flag, Table_Name_Exception, Table_Name_Condition_Value, DataCut_ID_Condition_Value) " & _
                  " Values ('" & ModelForm.ScenarioBox.SelectedValue.ToString & "', " & _
                  "'" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "', " & _
                  CurrentStage.ToString & ", " & _
                  "0, " & _
                  "'', " & _
                  "'" & CatBox1.SelectedValue.ToString & "', " & _
                  "'" & CutBox1.SelectedValue.ToString & "', " & _
                  "'" & ExceptionValue & "', " & _
                  "'no', " & _
                  "'', " & _
                  "'', " & _
                  "0," & ExceptionConstant & ",0,'" & ExceptCatBox1.SelectedValue & "', '', '')"
                Else
                    sql = "insert into Modelling_Steps_Products " & _
                  " (Scenario_ID, Product_Number, Step_Number, Operator, Table_Name, DataCut_ID, DataCut_ID_Exception, " & _
                  " Condition, Table_Name_Condition, DataCut_ID_Condition, Constant, Constant_Exception, Learner_Flag, Table_Name_Exception, Table_Name_Condition_Value, DataCut_ID_Condition_Value) " & _
                  " Values ('" & ModelForm.ScenarioBox.SelectedValue.ToString & "', " & _
                  CurrentStage.ToString & ", " & _
                  "0, " & _
                  "'', " & _
                  "'" & CatBox1.SelectedValue.ToString & "', " & _
                  "'" & CutBox1.SelectedValue.ToString & "', " & _
                  "'" & ExceptionValue & "', " & _
                  "'no', " & _
                  "'', " & _
                  "'', " & _
                  "0," & ExceptionConstant & ",0,'" & ExceptCatBox1.SelectedValue & "', '', '')"
                End If
            Else
                If Who = "Stage" Then
                    sql = "insert into Modelling_Steps " & _
                  " (Scenario_ID, Allocation_ID, Stage_Number, Step_Number, Operator, Table_Name, DataCut_ID, DataCut_ID_Exception, " & _
                  " Condition, Table_Name_Condition, DataCut_ID_Condition, Constant, Constant_Exception, Learner_Flag, Table_Name_Exception, Table_Name_Condition_Value, DataCut_ID_Condition_Value) " & _
                  " Values ('" & ModelForm.ScenarioBox.SelectedValue.ToString & "', " & _
                  "'" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "', " & _
                  CurrentStage.ToString & ", " & _
                  "0, " & _
                  "'', " & _
                  "'" & CatBox1.SelectedValue.ToString & "', " & _
                  "'" & "null" & "', " & _
                  "'" & ExceptionValue & "', " & _
                  "'no', " & _
                  "'', " & _
                  "'', " & _
                  CutBox1.Text & "," & ExceptionConstant & ",0,'" & ExceptCatBox1.SelectedValue & "', '', '')"
                Else
                    sql = "insert into Modelling_Steps_Products " & _
                  " (Scenario_ID, Product_Number, Step_Number, Operator, Table_Name, DataCut_ID, DataCut_ID_Exception, " & _
                  " Condition, Table_Name_Condition, DataCut_ID_Condition, Constant, Constant_Exception, Learner_Flag, Table_Name_Exception, Table_Name_Condition_Value, DataCut_ID_Condition_Value) " & _
                  " Values ('" & ModelForm.ScenarioBox.SelectedValue.ToString & "', " & _
                  CurrentStage.ToString & ", " & _
                  "0, " & _
                  "'', " & _
                  "'" & CatBox1.SelectedValue.ToString & "', " & _
                  "'" & "null" & "', " & _
                  "'" & ExceptionValue & "', " & _
                  "'no', " & _
                  "'', " & _
                  "'', " & _
                  CutBox1.Text & "," & ExceptionConstant & ",0,'" & ExceptCatBox1.SelectedValue & "', '', '')"
                End If
            End If

            RunNonQuery(sql)

        End If

        If LearnerCheckBox.Checked = True Then
            LearnerFlag = 1
        Else
            LearnerFlag = 0
        End If

        'exceptional constant
        Try
            ExceptionConstant = ExceptBox2.Text

        Catch ex As Exception
            'was not successful conversion so make it 0
            ExceptionConstant = 0
        End Try

        If IsNothing(ExceptBox2.SelectedValue) Then
            ExceptionValue = ""
        Else
            ExceptionValue = ExceptBox2.SelectedValue.ToString
        End If

        Dim ConditionVal As String = ""

        Try
            If CatBox4.SelectedValue = "zzConstant" Then
                ConditionVal = CutBox4.Text
            Else
                ConditionVal = CutBox4.SelectedValue.ToString
            End If
        Catch ex As Exception

        End Try
        

        If CatBox3.SelectedValue.ToString <> "zzConstant" Then
            If CatBox2.SelectedValue.ToString <> "zzConstant" Then
                If Who = "Stage" Then
                    sql = "insert into Modelling_Steps " & _
                      " (Scenario_ID, Allocation_ID, Stage_Number, Step_Number, Operator, Table_Name, DataCut_ID, DataCut_ID_Exception, " & _
                      " Condition, Table_Name_Condition, DataCut_ID_Condition, Constant, Constant_Exception, Learner_Flag, Table_Name_Exception, Table_Name_Condition_Value, DataCut_ID_Condition_Value) " & _
                      " Values ('" & ModelForm.ScenarioBox.SelectedValue.ToString & "', " & _
                      "'" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "', " & _
                      CurrentStage.ToString & ", " & _
                      CurrentStep.ToString & ", " & _
                      "'" & Mid(OperatorBox.Text, 1, 1) & "', " & _
                      "'" & CatBox2.SelectedValue.ToString & "', " & _
                      "'" & CutBox2.SelectedValue.ToString & "', " & _
                      "'" & ExceptionValue & "', " & _
                      "'" & Trim(Mid(IfBox.Text, 1, 2)) & "', " & _
                      "'" & CatBox3.SelectedValue.ToString & "', " & _
                      "'" & IIf(CutBox3.SelectedValue = Nothing, "", CutBox3.SelectedValue.ToString) & "', " & _
                      "0," & ExceptionConstant & "," & LearnerFlag.ToString & ",'" & ExceptCatBox2.SelectedValue & "', '" & CatBox4.SelectedValue & "', '" & ConditionVal & "')"
                Else
                    sql = "insert into Modelling_Steps_Products " & _
                      " (Scenario_ID,  Product_Number, Step_Number, Operator, Table_Name, DataCut_ID, DataCut_ID_Exception, " & _
                      " Condition, Table_Name_Condition, DataCut_ID_Condition, Constant, Constant_Exception, Learner_Flag, Table_Name_Exception, Table_Name_Condition_Value, DataCut_ID_Condition_Value) " & _
                      " Values ('" & ModelForm.ScenarioBox.SelectedValue.ToString & "', " & _
                      CurrentStage.ToString & ", " & _
                      CurrentStep.ToString & ", " & _
                      "'" & Mid(OperatorBox.Text, 1, 1) & "', " & _
                      "'" & CatBox2.SelectedValue.ToString & "', " & _
                      "'" & CutBox2.SelectedValue.ToString & "', " & _
                      "'" & ExceptionValue & "', " & _
                      "'" & Trim(Mid(IfBox.Text, 1, 2)) & "', " & _
                      "'" & CatBox3.SelectedValue.ToString & "', " & _
                      "'" & CutBox3.SelectedValue.ToString & "', " & _
                      "0," & ExceptionConstant & "," & LearnerFlag.ToString & ",'" & ExceptCatBox2.SelectedValue & "', '" & CatBox4.SelectedValue & "', '" & ConditionVal & "')"
                End If

            Else
                If Who = "Stage" Then
                    sql = "insert into Modelling_Steps " & _
                      " (Scenario_ID, Allocation_ID, Stage_Number, Step_Number, Operator, Table_Name, DataCut_ID, DataCut_ID_Exception, " & _
                      " Condition, Table_Name_Condition, DataCut_ID_Condition, Constant, Constant_Exception, Learner_Flag, Table_Name_Exception, Table_Name_Condition_Value, DataCut_ID_Condition_Value) " & _
                      " Values ('" & ModelForm.ScenarioBox.SelectedValue.ToString & "', " & _
                      "'" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "', " & _
                      CurrentStage.ToString & ", " & _
                      CurrentStep.ToString & ", " & _
                      "'" & Mid(OperatorBox.Text, 1, 1) & "', " & _
                      "'" & CatBox2.SelectedValue.ToString & "', " & _
                      "null, " & _
                      "'" & ExceptionValue & "', " & _
                      "'" & Trim(Mid(IfBox.Text, 1, 2)) & "', " & _
                      "'" & CatBox3.SelectedValue.ToString & "', " & _
                      "'" & IIf(CutBox3.SelectedValue = Nothing, "", CutBox3.SelectedValue.ToString) & "', " & _
                      CutBox2.Text & "," & ExceptionConstant & "," & LearnerFlag.ToString & ",'" & ExceptCatBox2.SelectedValue & "', '" & CatBox4.SelectedValue & "', '" & ConditionVal & "')"
                Else
                    sql = "insert into Modelling_Steps_Products " & _
                      " (Scenario_ID, Product_Number, Step_Number, Operator, Table_Name, DataCut_ID, DataCut_ID_Exception, " & _
                      " Condition, Table_Name_Condition, DataCut_ID_Condition, Constant, Constant_Exception, Learner_Flag, Table_Name_Exception, Table_Name_Condition_Value, DataCut_ID_Condition_Value) " & _
                      " Values ('" & ModelForm.ScenarioBox.SelectedValue.ToString & "', " & _
                      CurrentStage.ToString & ", " & _
                      CurrentStep.ToString & ", " & _
                      "'" & Mid(OperatorBox.Text, 1, 1) & "', " & _
                      "'" & CatBox2.SelectedValue.ToString & "', " & _
                      "null, " & _
                       "'" & ExceptionValue & "', " & _
                      "'" & Trim(Mid(IfBox.Text, 1, 2)) & "', " & _
                      "'" & CatBox3.SelectedValue.ToString & "', " & _
                      "'" & CutBox3.SelectedValue.ToString & "', " & _
                      CutBox2.Text & "," & ExceptionConstant & "," & LearnerFlag.ToString & ",'" & ExceptCatBox2.SelectedValue & "', '" & CatBox4.SelectedValue & "', '" & ConditionVal & "')"
                End If

            End If
        Else
            If CatBox2.SelectedValue.ToString <> "zzConstant" Then
                If Who = "Stage" Then
                    sql = "insert into Modelling_Steps " & _
                      " (Scenario_ID, Allocation_ID, Stage_Number, Step_Number, Operator, Table_Name, DataCut_ID, DataCut_ID_Exception, " & _
                      " Condition, Table_Name_Condition, DataCut_ID_Condition, Constant, Constant_Exception, Learner_Flag, Table_Name_Exception, Table_Name_Condition_Value, DataCut_ID_Condition_Value) " & _
                      " Values ('" & ModelForm.ScenarioBox.SelectedValue.ToString & "', " & _
                      "'" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "', " & _
                      CurrentStage.ToString & ", " & _
                      CurrentStep.ToString & ", " & _
                      "'" & Mid(OperatorBox.Text, 1, 1) & "', " & _
                      "'" & CatBox2.SelectedValue.ToString & "', " & _
                      "'" & CutBox2.SelectedValue.ToString & "', " & _
                      "'" & ExceptionValue & "', " & _
                      "'" & Trim(Mid(IfBox.Text, 1, 2)) & "', " & _
                      "'" & CatBox3.SelectedValue.ToString & "', " & _
                      "'" & CutBox3.Text & "', " & _
                      "0," & ExceptionConstant & "," & LearnerFlag.ToString & ",'" & ExceptCatBox2.SelectedValue & "', '" & CatBox4.SelectedValue & "', '" & ConditionVal & "')"
                Else
                    sql = "insert into Modelling_Steps_Products " & _
                      " (Scenario_ID,  Product_Number, Step_Number, Operator, Table_Name, DataCut_ID, DataCut_ID_Exception, " & _
                      " Condition, Table_Name_Condition, DataCut_ID_Condition, Constant, Constant_Exception, Learner_Flag, Table_Name_Exception, Table_Name_Condition_Value, DataCut_ID_Condition_Value) " & _
                      " Values ('" & ModelForm.ScenarioBox.SelectedValue.ToString & "', " & _
                      CurrentStage.ToString & ", " & _
                      CurrentStep.ToString & ", " & _
                      "'" & Mid(OperatorBox.Text, 1, 1) & "', " & _
                      "'" & CatBox2.SelectedValue.ToString & "', " & _
                      "'" & CutBox2.SelectedValue.ToString & "', " & _
                      "'" & ExceptionValue & "', " & _
                      "'" & Trim(Mid(IfBox.Text, 1, 2)) & "', " & _
                      "'" & CatBox3.SelectedValue.ToString & "', " & _
                      "'" & CutBox3.Text & "', " & _
                      "0," & ExceptionConstant & "," & LearnerFlag.ToString & ",'" & ExceptCatBox2.SelectedValue & "', '" & CatBox4.SelectedValue & "', '" & ConditionVal & "')"
                End If

            Else
                If Who = "Stage" Then
                    sql = "insert into Modelling_Steps " & _
                      " (Scenario_ID, Allocation_ID, Stage_Number, Step_Number, Operator, Table_Name, DataCut_ID, DataCut_ID_Exception, " & _
                      " Condition, Table_Name_Condition, DataCut_ID_Condition, Constant, Constant_Exception, Learner_Flag, Table_Name_Exception, Table_Name_Condition_Value, DataCut_ID_Condition_Value) " & _
                      " Values ('" & ModelForm.ScenarioBox.SelectedValue.ToString & "', " & _
                      "'" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "', " & _
                      CurrentStage.ToString & ", " & _
                      CurrentStep.ToString & ", " & _
                      "'" & Mid(OperatorBox.Text, 1, 1) & "', " & _
                      "'" & CatBox2.SelectedValue.ToString & "', " & _
                      "null, " & _
                       "'" & ExceptionValue & "', " & _
                      "'" & Trim(Mid(IfBox.Text, 1, 2)) & "', " & _
                      "'" & CatBox3.SelectedValue.ToString & "', " & _
                      "'" & CutBox3.Text & "', " & _
                      CutBox2.Text & "," & ExceptionConstant & "," & LearnerFlag.ToString & ",'" & ExceptCatBox2.SelectedValue & "', '" & CatBox4.SelectedValue & "', '" & ConditionVal & "')"
                Else
                    sql = "insert into Modelling_Steps_Products " & _
                      " (Scenario_ID, Product_Number, Step_Number, Operator, Table_Name, DataCut_ID, DataCut_ID_Exception, " & _
                      " Condition, Table_Name_Condition, DataCut_ID_Condition, Constant, Constant_Exception, Learner_Flag, Table_Name_Exception, Table_Name_Condition_Value, DataCut_ID_Condition_Value) " & _
                      " Values ('" & ModelForm.ScenarioBox.SelectedValue.ToString & "', " & _
                      CurrentStage.ToString & ", " & _
                      CurrentStep.ToString & ", " & _
                      "'" & Mid(OperatorBox.Text, 1, 1) & "', " & _
                      "'" & CatBox2.SelectedValue.ToString & "', " & _
                      "null, " & _
                       "'" & ExceptionValue & "', " & _
                      "'" & Trim(Mid(IfBox.Text, 1, 2)) & "', " & _
                      "'" & CatBox3.SelectedValue.ToString & "', " & _
                      "'" & CutBox3.Text & "', " & _
                      CutBox2.Text & "," & ExceptionConstant & "," & LearnerFlag.ToString & ",'" & ExceptCatBox2.SelectedValue & "', '" & CatBox4.SelectedValue & "', '" & ConditionVal & "')"
                End If

            End If
        End If



        RunNonQuery(sql)

    End Sub

    Private Sub SaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveButton.Click
        Dim TheCurrentStep As Integer

        'validate the exception boxes, because they can be blank
        If Step1Panel.Visible = True Then
            If ExceptBox1.SelectedIndex < 0 Then
                If ExceptCatBox1.Text <> "Constant" Then
                    MessageBox.Show("You cannot have an exception data cut that has no selection, please make sure that you select a data cut or that you select <None>", "Exception data cut cannot be blank", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Exit Sub
                End If

            End If
        End If

        If ExceptBox2.SelectedIndex < 0 Then
            If ExceptCatBox2.Text <> "Constant" Then
                MessageBox.Show("You cannot have an exception data cut that has no selection, please make sure that you select a data cut or that you select <None>", "Exception data cut cannot be blank", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit Sub
            End If
        End If


        TheCurrentStep = CurrentStep

        ButtonPanel.Enabled = True
        StepsGrid.Enabled = True
        SaveStep()
        AddMode = False
        LoadStepsGrid()

        'now set the grid back to the current step
        If StepsGrid.RowCount <> 0 Then
            StepsGrid.CurrentCell = StepsGrid.Item(StepsGrid.Columns("Step").Index, TheCurrentStep)

        End If
    End Sub

    Private Sub ResetSteps()
        If StepsGrid.RowCount = 0 Then Exit Sub


        If StepsGrid.CurrentRow.Index = 1 Then
            CurrentStep = 1
        Else
            If StepsGrid.CurrentRow.Index = 0 Then
                CurrentStep = 1
            Else
                CurrentStep = StepsGrid.CurrentRow.Index
            End If
        End If
    End Sub

    Private Sub StepsGrid_CellEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles StepsGrid.CellEnter
        If AddMode = True Then Exit Sub

        ResetSteps()

        LoadStep(CurrentStep)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        AddMode = False
        ButtonPanel.Enabled = True
        StepsGrid.Enabled = True

        ResetSteps()


        LoadStep(CurrentStep)
    End Sub

    Private Sub StepsGrid_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles StepsGrid.CellFormatting
        If StepsGrid.Columns(e.ColumnIndex).Name = "O" Then
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            e.CellStyle.Font = New Font("Arial", 14, FontStyle.Bold)
        End If

        If StepsGrid.Columns(e.ColumnIndex).Name = "Step" Then
            e.CellStyle.BackColor = StepHeaderPanel.BackColor
            e.CellStyle.Font = New Font("Arial", 10, FontStyle.Bold)
            e.CellStyle.ForeColor = Color.White

        End If

        If StepsGrid.Columns(e.ColumnIndex).Name = "Part1" Then
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End If

        If StepsGrid.Columns(e.ColumnIndex).Name = "ThePic" Then
            If StepsGrid.Item(StepsGrid.Columns("Learner_Flag").Index, e.RowIndex).Value = 1 Then
                e.Value = TheImages.Images(0)
            Else
                e.Value = TheImages.Images(1)
            End If
        End If

    End Sub

    Private Sub DescriptionBox_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DescriptionBox.Leave
        Dim sql As String

        If Who = "Stage" Then
            sql = "update Modelling_Stages " & _
                 "set Stage_Description = '" & DescriptionBox.Text & "' " & _
                 "where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' and Allocation_ID = '" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "' " & _
                 "and Stage_Number = " & CurrentStage.ToString
        Else
            If Trim(DescriptionBox.Text) = "" Then
                'MessageBox.Show("You must enter a name for this product first", "Please enter a name", MessageBoxButtons.OK, MessageBoxIcon.Error)
                '   DescriptionBox.Focus()
                Exit Sub

            End If

            sql = "update Modelling_Products " & _
                 "set Product_Description = '" & DescriptionBox.Text & "' " & _
                 "where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' " & _
                 "and Product_Number = " & CurrentStage.ToString
        End If


        RunNonQuery(sql)

        If DescriptionBox.Text <> "" Then
            ReminderPanel.Visible = False
        Else
            ReminderPanel.Visible = True
        End If

    End Sub


    Private Sub StageForm_Activated(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Activated
        If DescriptionBox.Text <> "" Then
            ReminderPanel.Visible = False
        Else
            ReminderPanel.Visible = True
        End If

        DescriptionBox.Focus()

    End Sub

    Private Sub CutBox2_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CutBox2.Leave
        Dim i As Decimal

        If CatBox2.SelectedValue = "zzConstant" Then
            Try
                i = CutBox2.Text
            Catch ex As Exception
                MessageBox.Show("The value you entered is not valid.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                CutBox2.Focus()
            End Try
        End If
    End Sub

    Private Sub DeleteBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteBut.Click
        Dim sql As String

        If StepsGrid.RowCount = 0 Then Exit Sub

        If StepsGrid.CurrentRow.Index <> StepsGrid.RowCount - 1 Then
            MessageBox.Show("You can only delete the last step", "Can not delete this step", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Else
            If MessageBox.Show("Are you sure you want to delete " & _
                               StepsGrid.Item(StepsGrid.Columns("Step").Index, StepsGrid.CurrentRow.Index).Value.ToString & "?", _
                               "Delete current step?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

                If Who = "Stage" Then
                    sql = "delete from Modelling_Steps where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' " & _
             " and Stage_Number = " & CurrentStage.ToString & _
             " and Step_Number = " & CurrentStep.ToString & _
             " and Allocation_ID = '" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "' "

                Else
                    sql = "delete from Modelling_Steps_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' " & _
             " and Product_Number = " & CurrentStage.ToString & _
             " and Step_Number = " & CurrentStep.ToString
                End If

                RunNonQuery(sql)

                If CurrentStep = 1 Then
                    If Who = "Stage" Then
                        sql = "delete from Modelling_Steps where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' " & _
                     " and Stage_Number = " & CurrentStage.ToString & _
                     " and Step_Number = 0 " & _
                     " and Allocation_ID = '" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "' "

                    Else
                        sql = "delete from Modelling_Steps_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' " & _
                                             " and Product_Number = " & CurrentStage.ToString & _
                                             " and Step_Number = 0 "
                    End If

                    RunNonQuery(sql)
                End If
                LoadStepsGrid()

                If StepsGrid.RowCount <> 0 Then
                    StepsGrid.CurrentCell = StepsGrid.Item(StepsGrid.Columns("Step").Index, 0)
                    ResetSteps()

                End If

            End If
        End If
    End Sub

    Private Sub ResultsBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ResultsBut.Click
        Dim sql As String

        ShowLoadingForm()
        Job("Loading Results...")


        With Modelling_ResultsForm
            
            sql = .StageSQL(ModelForm.ScenarioBox.SelectedValue.ToString, ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString _
                                           , CurrentStage.ToString, Who, True)
            If Who = "Stage" Then
                With ModelForm
                    sql = sql & " where R.UPIN in (select UPIN from Provider_Funding_Streams where period_ID = " & .PeriodBox.SelectedValue & " and Budget_ID = '" & _
                              .AllocationLinesGrid.Item(.AllocationLinesGrid.Columns("Budget_ID").Index, .AllocationLinesGrid.CurrentRow.Index).Value.ToString & "')"
                    sql = sql & " and R.Lead = 1 "
                End With

            Else
                sql = sql & " where R.Lead = 1 and R.UPIN in (Select distinct UPIN from Provider_Funding_Streams where period_ID = " & ModelForm.PeriodBox.SelectedValue & ") "
            End If



            .ResultsGrid.AutoGenerateColumns = True
            .ResultsGrid.Columns.Clear()
            .TheBS.DataSource = LoadDataTable(sql)

            .Text = Me.Text & " Results"

            .Show()
            .Focus()


        End With
        HideLoadingForm()
        Job("Ready")

    End Sub

    Private Sub CutBox1_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CutBox1.Leave
        Dim i As Decimal

        If CatBox1.SelectedValue = "zzConstant" Then
            Try
                i = CutBox1.Text
            Catch ex As Exception
                MessageBox.Show("The value you entered is not valid.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                CutBox1.Focus()
            End Try
        End If
    End Sub

    Private Sub ExceptCatBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExceptCatBox1.SelectedIndexChanged
        LoadExceptCutBox1()

    End Sub

    Private Sub ExceptCatBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExceptCatBox2.SelectedIndexChanged
        LoadExceptCutBox2()
    End Sub

    Private Sub CatBox4_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CatBox4.SelectedIndexChanged
        LoadCutBox4()

    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Dim sql As String

        With Modelling_Process
            .Text = Me.Text
            If Who = "Stage" Then

                sql = "select * from dbo.Modelling_Stages where scenario_id = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "'" & _
                    " and Allocation_ID = '" & ModelForm.AllocationLinesGrid.Item(ModelForm.AllocationLinesGrid.Columns("ID").Index, ModelForm.AllocationLinesGrid.CurrentRow.Index).Value.ToString & "'" & _
                    " and Stage_Number = " & CurrentStage.ToString & _
                    " order by Allocation_ID, Stage_Number"

                .StagesSQL = sql
                .IsStage = True

            Else

                sql = "select * from dbo.Modelling_Products where scenario_id = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "'" & _
                   " and Product_Number = " & CurrentStage.ToString & _
                   " order by Product_Number"
                .StagesSQL = sql

                .IsStage = False


            End If
            .IsAllocationLine = False
            .TheRID = ""
            .CanClearTempData = True
            .ReturnSQL = False
            .Session_ID_Main = ""
            .Process_Period_ID = ModelForm.PeriodBox.SelectedValue
            .ShowDialog()

        End With


    End Sub
End Class
